/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package AccesoADatos;

import Logica.Link;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import javax.swing.JOptionPane;

/**
 *
 * @author Fernando
 */
public class ServicioLink extends Servicio {

    private String user;
    private String pass;
    private String ip;
    private String port;
    private String db;
    private static String LINKS = "SELECT DB_LINK FROM DBA_DB_LINKS";
    private static String TAKEUSER = "SELECT * FROM DBA_DB_LINKS WHERE DB_LINK =?";

    public ServicioLink(String user, String pass, String ip, String port, String db) {
        this.user = user;
        this.pass = pass;
        this.ip = ip;
        this.port = port;
        this.db = db;

    }

    public boolean iniciarConexion() throws GlobalException, NoDataException, SQLException {
        try {
            conectar(user, pass, ip, port, db);
            return true;
        } catch (ClassNotFoundException ex) {
            return false;
        }

    }

    public String crearQuery(Link l) {
        //String n = "CREATE DATABASE LINK LINK1A CONNECT TO estudiante IDENTIFIED BY estudiante USING '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = LAB1004-PCprofe)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE) ))'";
        StringBuilder s = new StringBuilder();
        s.append(String.format("CREATE DATABASE LINK "));
        s.append(String.format(l.getNameLink()));
        s.append(String.format(" CONNECT TO "));
        s.append(String.format(l.getUser()));
        s.append(String.format(" IDENTIFIED BY "));
        s.append(String.format(l.getPass()));
        s.append(String.format(" USING '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = "));
        s.append(String.format(l.getHost()));
        s.append(String.format(")(PORT = "));
        s.append(String.format(l.getPort()));
        s.append(String.format(")) (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = "));
        s.append(String.format(l.getDb()));
        s.append(String.format(")))'"));
        return s.toString();

    }

    public boolean newDatabaseLink(Link ln) throws GlobalException, NoDataException {
        try {
            conectar(user, pass, ip, port, db);
        } catch (ClassNotFoundException ex) {
            throw new GlobalException("No se ha localizado el Driver");
        } catch (SQLException e) {
            throw new NoDataException("La base de datos no se encuentra disponible");
        }

        ResultSet rs = null;
        Statement stmt = null;
        try {
            stmt = conexion.createStatement();
            String ss = this.crearQuery(ln);
            rs = stmt.executeQuery(ss);
            //JOptionPane.showMessageDialog(null, ss);
        } catch (SQLException e) {
            //return false;
            e.printStackTrace();

            throw new GlobalException("Sentencia no valida");
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                    rs.close();
                }
                desconectar();
                return true;
            } catch (SQLException e) {
                throw new GlobalException("Estatutos invalidos o nulos");
            }
        }
    }

    public Collection listarLinks() throws GlobalException, NoDataException {
        try {
            conectar(user, pass, ip, port, db);
        } catch (ClassNotFoundException ex) {
            throw new GlobalException("No se ha localizado el Driver");
        } catch (SQLException e) {
            throw new NoDataException("La base de datos no se encuentra disponible");
        }

        ResultSet rs = null;
        ArrayList coleccion = new ArrayList();
        PreparedStatement stmt = null;
        try {
            stmt = conexion.prepareStatement(LINKS);
            rs = stmt.executeQuery();
            while (rs.next()) {
                String aux = rs.getString("DB_LINK");
                if (!(aux.equals("DBMS_CLRDBLINK"))) {
                    coleccion.add(aux);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();

            throw new GlobalException("Sentencia no valida");
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                desconectar();
            } catch (SQLException e) {
                throw new GlobalException("Estatutos invalidos o nulos");
            }
        }
        if (coleccion == null || coleccion.size() == 0) {
            throw new NoDataException("No hay datos");
        }
        return coleccion;
    }
    
    
    public String tomarUser(String dbl) throws GlobalException, NoDataException {
        try {
            conectar(user, pass, ip, port, db);
        } catch (ClassNotFoundException ex) {
            throw new GlobalException("No se ha localizado el Driver");
        } catch (SQLException e) {
            throw new NoDataException("La base de datos no se encuentra disponible");
        }
        String aux = null;
        ResultSet rs = null;
        PreparedStatement stmt = null;
        
        
        try {
            stmt = conexion.prepareStatement(TAKEUSER);
            stmt.setString(1,dbl);
            rs = stmt.executeQuery();
            while (rs.next()) {
                aux = rs.getString("USERNAME");
            }


        } catch (SQLException e) {
            e.printStackTrace();

            throw new GlobalException("Sentencia no valida");
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                desconectar();
            } catch (SQLException e) {
                throw new GlobalException("Estatutos invalidos o nulos");
            }
        }
        
        return aux;
    }
}
